﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using VswService_V7.DataBase.Tables.L2INTERFACE;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    public partial class TASK_INFO
    {

        #region field name 
        public static readonly string tableName = "TASK_INFO";
        public static readonly string fd_eventNo = "EVENT_NO";
        public static readonly string fd_taskNo = "TASK_NO";
        public static readonly string fd_pipeQuantityBatch = "PIPE_QUANTITY_BATCH";
        public static readonly string fd_produceDestination = "PRODUCE_DESTINATION";
        public static readonly string fd_priority = "PRIORITY";
        public static readonly string fd_batchSampleRule = "BATCH_SAMPLE_RULE";
        public static readonly string fd_stripSampleRule = "STRIP_SAMPLE_RULE";
        public static readonly string fd_recipeNo = "RECIPE_NO";
        public static readonly string fd_productionStandard = "PRODUCTION_STANDARD";
        public static readonly string fd_pipeDurabilityClass = "PIPE_DURABILITY_CLASS";
        public static readonly string fd_pipeSteelGrade = "PIPE_STEEL_GRADE";
        public static readonly string fd_pipeNominalMass = "PIPE_NOMINAL_MASS";
        public static readonly string fd_pipeNominalLength = "PIPE_NOMINAL_LENGTH";
        public static readonly string fd_pipeMaxLength = "PIPE_MAX_LENGTH";
        public static readonly string fd_pipeMinLength = "PIPE_MIN_LENGTH";
        public static readonly string fd_pipeNominalOd = "PIPE_NOMINAL_OD";
        public static readonly string fd_pipeMaxOd = "PIPE_MAX_OD";
        public static readonly string fd_pipeMinOd = "PIPE_MIN_OD";
        public static readonly string fd_pipeNominalThickness = "PIPE_NOMINAL_THICKNESS";
        public static readonly string fd_pipeMinThickness = "PIPE_MIN_THICKNESS";
        public static readonly string fd_innerBeadIsremoving = "INNER_BEAD_ISREMOVING";
        public static readonly string fd_innerBeadHeight = "INNER_BEAD_HEIGHT";
        public static readonly string fd_innerBeadDepth = "INNER_BEAD_DEPTH";
        public static readonly string fd_ovalityUpLimit = "OVALITY_UP_LIMIT";
        public static readonly string fd_bodyStraightness = "BODY_STRAIGHTNESS";
        public static readonly string fd_endStraightness = "END_STRAIGHTNESS";
        public static readonly string fd_status = "STATUS";
        public static readonly string fd_updateTime = "UPDATE_TIME";

        #endregion


        /// <summary>
        /// query task information according to taskno
        /// </summary>
        /// <param name="task_No">taskNo</param>
        /// <returns></returns>
        public static DataTable SelectTaskInfo(string task_No)
        {
            try
            {
                string sql = string.Format("select * from {0} where {1}='{2}'", tableName, fd_taskNo, task_No);
                DataTable dt = OracleHelper.Exct_QueryTable(sql);
                return dt;

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectTaskInfo", ex);
                throw ex;
            }
        }


        /// <summary>
        ///Determine the status of the task by stripNo and modify it
        /// </summary>
        /// <param name="stripsNoList"></param>
        /// <returns></returns>
        public static void UpdateTaskStatus(string stripNo)
        {
            try
            {
                string sql = string.Format("select * from {0} a,(select {1}, {2},{3} as {4} from {5} where {6} in (select {7} from {8} where {9}='{10}')) b where a.{11}=b.{12}",
                     TASK_INFO.tableName, STRIP_INFO.fd_taskNo, STRIP_INFO.fd_stripNo, STRIP_INFO.fd_status, "STRIP_STATUS", STRIP_INFO.tableName, STRIP_INFO.fd_taskNo, STRIP_INFO.fd_taskNo, STRIP_INFO.tableName, STRIP_INFO.fd_stripNo, stripNo, fd_taskNo, STRIP_INFO.fd_taskNo);
                DataTable taskTable = OracleHelper.Exct_QueryTable(sql);
                string preStatus = taskTable.Rows[0][fd_status].ToString();
                string changedStatus = "";
                string taskNo = taskTable.Rows[0][STRIP_INFO.fd_taskNo].ToString();
                DataRow[] dataRowsLoaded = taskTable.Select(string.Format("STRIP_STATUS='{0}'", STRIP_INFO.Loaded));
                DataRow[] dataRowsFinished = taskTable.Select(string.Format("STRIP_STATUS='{0}'", STRIP_INFO.finished));
                if (dataRowsLoaded.Length > 0)
                {
                    changedStatus = started;
                }
                if (dataRowsFinished.Length == taskTable.Rows.Count)
                {
                    changedStatus = finished;
                }
                if ((preStatus != changedStatus)&& !string.IsNullOrEmpty(changedStatus))  //if (preStatus != changedStatus)
                {
                    long eventNo = OracleHelper.GetNextEventNo();
                    //string sqlTASK = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, started, fd_taskNo, taskNo);
                    string sqlTASK = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, changedStatus, fd_taskNo, taskNo);
                    string sqlINTFTASK = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}')",
                    L2INTF_TASK_INFO.tableName, eventNo, taskTable.Rows[0][fd_taskNo].ToString(), taskTable.Rows[0][fd_pipeQuantityBatch].ToString(), taskTable.Rows[0][fd_produceDestination].ToString(), taskTable.Rows[0][fd_priority].ToString(), taskTable.Rows[0][fd_batchSampleRule].ToString(), taskTable.Rows[0][fd_stripSampleRule].ToString(),
                    taskTable.Rows[0][fd_recipeNo].ToString(), taskTable.Rows[0][fd_productionStandard].ToString(), taskTable.Rows[0][fd_pipeDurabilityClass].ToString(), taskTable.Rows[0][fd_pipeSteelGrade].ToString(), taskTable.Rows[0][fd_pipeNominalMass].ToString(), taskTable.Rows[0][fd_pipeNominalLength].ToString(),
                    taskTable.Rows[0][fd_pipeMaxLength].ToString(), taskTable.Rows[0][fd_pipeMinLength].ToString(), taskTable.Rows[0][fd_pipeNominalOd].ToString(), taskTable.Rows[0][fd_pipeMaxOd].ToString(), taskTable.Rows[0][fd_pipeMinOd].ToString(), taskTable.Rows[0][fd_pipeNominalThickness].ToString(),
                    taskTable.Rows[0][fd_pipeMinThickness].ToString(), taskTable.Rows[0][fd_innerBeadIsremoving].ToString(), taskTable.Rows[0][fd_innerBeadHeight].ToString(), taskTable.Rows[0][fd_innerBeadDepth].ToString(), taskTable.Rows[0][fd_ovalityUpLimit].ToString(), taskTable.Rows[0][fd_bodyStraightness].ToString(),
                    taskTable.Rows[0][fd_endStraightness].ToString(), taskTable.Rows[0][fd_status].ToString(), taskTable.Rows[0][fd_updateTime].ToString());

                    //string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                    //    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.editTask, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");

                    //need not inform L3,only record in L2 2022-03-25
                    string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                        L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.editTask, DateTime.Now.ToString(Formats.GetDateFormat()), "2", "L1", "");

                    List<string> sqlList = new List<string>();
                    sqlList.Add(sqlTASK);
                    sqlList.Add(sqlINTFTASK);
                    sqlList.Add(sqlINTFEVENT);
                    OracleHelper.ExecuteSqlTran(sqlList);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateTaskStatus", ex);
                throw ex;
            }
        }

        /// <summary>
        /// Determine the status of the task by stripNo and modify it
        /// </summary>
        /// <param name="stripNo"></param>
        public static void UpdateTaskStatus2(string stripNo)
        {
            try
            {
                string inquireTaskSql = string.Format($"select * from {TASK_INFO.tableName} where {STRIP_INFO.fd_stripNo}='{stripNo}'");
                DataTable task = OracleHelper.Exct_QueryTable(inquireTaskSql);
                
                if (task.Rows.Count == 1)
                {
                    string taskNo = task.Rows[0][fd_taskNo].ToString();
                    string currentTaskStatus = task.Rows[0][fd_status].ToString();

                    string inquireStripSql = string.Format($"select * from {STRIP_INFO.tableName} where {STRIP_INFO.fd_taskNo}='{taskNo}'");
                    DataTable strips = OracleHelper.Exct_QueryTable(inquireStripSql);

                    DataRow[] stripsLoaded = strips.Select(string.Format("STRIP_STATUS='{0}'", STRIP_INFO.Loaded));
                    DataRow[] stripsFinished = strips.Select(string.Format("STRIP_STATUS='{0}'", STRIP_INFO.finished));

                    string taskStatus = started;
                    if (stripsLoaded.Length > 0)
                    {
                        taskStatus = started;
                    }
                    if (stripsFinished.Length == strips.Rows.Count)
                    {
                        taskStatus = finished;
                    }
                    if (currentTaskStatus != taskStatus)
                    {
                        long eventNo = OracleHelper.GetNextEventNo();
                        //string sqlTASK = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, started, fd_taskNo, taskNo);
                        string sqlTASK = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, taskStatus, fd_taskNo, taskNo);
                        string sqlINTFTASK = string.Format($"insert into {L2INTF_TASK_INFO.tableName} values({eventNo},'{task.Rows[0][fd_taskNo].ToString()}','{task.Rows[0][fd_pipeQuantityBatch].ToString()}','{task.Rows[0][fd_produceDestination].ToString()}'," +
                            $"'{task.Rows[0][fd_priority].ToString()}','{task.Rows[0][fd_batchSampleRule].ToString()}','{task.Rows[0][fd_stripSampleRule].ToString()}','{task.Rows[0][fd_recipeNo].ToString()}','{task.Rows[0][fd_productionStandard].ToString()}'" +
                            $",'{task.Rows[0][fd_pipeDurabilityClass].ToString()}','{task.Rows[0][fd_pipeSteelGrade].ToString()}','{task.Rows[0][fd_pipeNominalMass].ToString()}','{task.Rows[0][fd_pipeNominalLength].ToString()}','{task.Rows[0][fd_pipeMaxLength].ToString()}'" +
                            $",'{task.Rows[0][fd_pipeMinLength].ToString()}','{task.Rows[0][fd_pipeNominalOd].ToString()}','{task.Rows[0][fd_pipeMaxOd].ToString()}','{task.Rows[0][fd_pipeMinOd].ToString()}','{task.Rows[0][fd_pipeNominalThickness].ToString()}'" +
                            $",'{task.Rows[0][fd_pipeMinThickness].ToString()}','{task.Rows[0][fd_innerBeadIsremoving].ToString()}','{task.Rows[0][fd_innerBeadHeight].ToString()}','{task.Rows[0][fd_innerBeadDepth].ToString()}','{task.Rows[0][fd_ovalityUpLimit].ToString()}'" +
                            $",'{task.Rows[0][fd_bodyStraightness].ToString()}','{task.Rows[0][fd_endStraightness].ToString()}','{task.Rows[0][fd_status].ToString()}','{task.Rows[0][fd_updateTime].ToString()}')");

                        //string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                        //    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.editTask, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");

                        //need not inform L3,only record in L2 2022-03-25
                        string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                            L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.editTask, DateTime.Now.ToString(Formats.GetDateFormat()), "2", "L1", "");

                        List<string> sqlList = new List<string>();
                        sqlList.Add(sqlTASK);
                        sqlList.Add(sqlINTFTASK);
                        sqlList.Add(sqlINTFEVENT);
                        OracleHelper.ExecuteSqlTran(sqlList);
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateTaskStatus", ex);
                throw ex;
            }
        }




        /// <summary>
        /// Determine the status of the task by taskNo and modify it
        /// </summary>
        /// <param name="taskNo"></param>
        public static void UpdateTaskStatusByTaskNo(string taskNo)
        {
            try
            {
                string sql = string.Format("select * from {0} where {1}='{2}'",
                     tableName, fd_taskNo, taskNo);
                DataTable taskTable = OracleHelper.Exct_QueryTable(sql);
                string preStatus = taskTable.Rows[0][fd_status].ToString();
                string changedStatus = "";
                string sqlstrip = string.Format("select * from {0} where {1}='{2}'",
                     STRIP_INFO.tableName, STRIP_INFO.fd_taskNo, taskNo);
                DataTable stripTable = OracleHelper.Exct_QueryTable(sql);
                DataRow[] dataRowsLoaded = stripTable.Select(string.Format("{0}='{1}'", STRIP_INFO.fd_status, STRIP_INFO.Loaded));
                DataRow[] dataRowsFinished = stripTable.Select(string.Format("{0}='{1}'", STRIP_INFO.fd_status, STRIP_INFO.finished));
                if (dataRowsLoaded.Length > 0)
                {
                    changedStatus = started;
                }
                if (dataRowsFinished.Length == taskTable.Rows.Count)
                {
                    changedStatus = finished;
                }
                if (preStatus != changedStatus)
                {
                    long eventNo = OracleHelper.GetNextEventNo();
                    string sqlTASK = string.Format("update {0} set {1}='{2}' where {3}='{4}'", tableName, fd_status, started, fd_taskNo, taskNo);
                    string sqlINTFTASK = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}')",
                    L2INTF_TASK_INFO.tableName, eventNo, taskTable.Rows[0][fd_taskNo].ToString(), taskTable.Rows[0][fd_pipeQuantityBatch].ToString(), taskTable.Rows[0][fd_produceDestination].ToString(), taskTable.Rows[0][fd_priority].ToString(), taskTable.Rows[0][fd_batchSampleRule].ToString(), taskTable.Rows[0][fd_stripSampleRule].ToString(),
                    taskTable.Rows[0][fd_recipeNo].ToString(), taskTable.Rows[0][fd_productionStandard].ToString(), taskTable.Rows[0][fd_pipeDurabilityClass].ToString(), taskTable.Rows[0][fd_pipeSteelGrade].ToString(), taskTable.Rows[0][fd_pipeNominalMass].ToString(), taskTable.Rows[0][fd_pipeNominalLength].ToString(),
                    taskTable.Rows[0][fd_pipeMaxLength].ToString(), taskTable.Rows[0][fd_pipeMinLength].ToString(), taskTable.Rows[0][fd_pipeNominalOd].ToString(), taskTable.Rows[0][fd_pipeMaxOd].ToString(), taskTable.Rows[0][fd_pipeMinOd].ToString(), taskTable.Rows[0][fd_pipeNominalThickness].ToString(),
                    taskTable.Rows[0][fd_pipeMinThickness].ToString(), taskTable.Rows[0][fd_innerBeadIsremoving].ToString(), taskTable.Rows[0][fd_innerBeadHeight].ToString(), taskTable.Rows[0][fd_innerBeadDepth].ToString(), taskTable.Rows[0][fd_ovalityUpLimit].ToString(), taskTable.Rows[0][fd_bodyStraightness].ToString(),
                    taskTable.Rows[0][fd_endStraightness].ToString(), taskTable.Rows[0][fd_status].ToString(), taskTable.Rows[0][fd_updateTime].ToString());

                    //string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                    //    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.editTask, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");

                    //need not inform L3,only record in L2 2022-03-25
                    string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                        L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.editTask, DateTime.Now.ToString(Formats.GetDateFormat()), "2", "L1", "");

                    List<string> sqlList = new List<string>();
                    sqlList.Add(sqlTASK);
                    sqlList.Add(sqlINTFTASK);
                    sqlList.Add(sqlINTFEVENT);
                    OracleHelper.ExecuteSqlTran(sqlList);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateTaskStatus", ex);
                throw ex;
            }
        }
    }

}
